/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Manager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

/**
 *
 * @author Overpower
 */

public class BDManager
{
    private static final String SQL_DRIVER = "com.mysql.jdbc.Driver";
    
    private static BDManager _instancia = null;
    
    private String _user;
    private String _pass;
    private String _url;
    private String _dataBase;
    
    Connection _connection;
    private boolean _connected;
    
    private class Transaction
    {
        private Statement _statement;
        public ResultSet result;
        
        public void close() throws SQLException
        {
            if( null != result )
                result.close();
            if( null != _statement ) 
                _statement.close();
            
            result = null;
            _statement = null;
        }
    }
    
    public BDManager()
    {
        _connected = false;
    }
    
    public static BDManager Get()
    {
        if( null == _instancia )
            _instancia = new BDManager();
        return _instancia;
    }
    
    public void Setup( String _url, String _dbName )
    {
        this._url = _url;
        this._dataBase = _dbName;
    }
    
    public String Connectar( String _user, String _pass )
    {
        String _err = "";
        if( _connected )
            return _err;
        
        try {
            Class.forName(SQL_DRIVER);
        } catch (ClassNotFoundException ex) {
            _err += ex.getMessage();
        }
        
        try {
            _connection = DriverManager.getConnection( "jdbc:mysql://" + this._url + "/" + this._dataBase + "?user=" + _user + "&password=" + _pass );
            _connected = true;
        } catch (SQLException ex) {
            _err += "No se pudo conectar con la Base de datos: " + ex.getMessage();
        }
        
        if( _connection != null )
        {
            this._user = _user;
            this._pass = _pass;
        }
        
        return _err;
    }
    
    /**
     *
     * @param _campos
     * @param _tabla
     * @return
     */
    private ArrayList<HashMap<String, Object>> _CrearHashTable( ResultSet _result ) throws SQLException
    {
        ArrayList<HashMap<String, Object>> _hashList = new ArrayList<>();
        
        while( _result.next() )
        {
            ResultSetMetaData _meta = _result.getMetaData();
            Integer _columns = _meta.getColumnCount();
            HashMap<String, Object> _hash = new HashMap<>();
            for( int i = 1; i <= _columns; i++ )
            {
                Object put = _hash.put( _meta.getColumnName(i) , _result.getObject(i) );
            }
            _hashList.add(_hash);
        }
        return _hashList;
    }
    
    private Transaction _ExecuteQuery( String _sqlQuerry ) throws SQLException
    {  
        Transaction _transaction = new Transaction();
        
        _transaction._statement = (Statement)_connection.createStatement();
        _transaction.result = _transaction._statement.executeQuery(_sqlQuerry);
        return _transaction;
    }
    
    private void _ExecuteUpdate( String _sqlString ) throws SQLException
    {  
        Transaction _transaction = new Transaction();
        
        _transaction._statement = (Statement)_connection.createStatement();
        _transaction._statement.executeUpdate(_sqlString);
        _transaction.close();
    }
    
    public ArrayList<HashMap<String, Object>> Select( String _campos, String _tabla ) throws SQLException
    {
        if( !_connected )return null;
        ArrayList<HashMap<String, Object>> _hashMap = null;
       
        Transaction _transaction = _ExecuteQuery("SELECT " + _campos + " FROM " + _tabla);
        _hashMap = _CrearHashTable(_transaction.result);
        _transaction.close();
        
        return _hashMap;
    }
    
    public ArrayList<HashMap<String, Object>> Select( String _campos, String _tabla, String _where ) throws SQLException
    {
        if( !_connected )return null;
        ArrayList<HashMap<String, Object>> _hashMap = null;
       
        Transaction _transaction = _ExecuteQuery("SELECT " + _campos + " FROM " + _tabla + " WHERE " + _where );
        _hashMap = _CrearHashTable(_transaction.result);
        _transaction.close();
        
        return _hashMap;
    }
    
    public String Insert( String _tabla, HashMap<String, String> _map )
    {
        /*
        INSERT INTO `gestioncesmi`.`persona` (`idPersona`, `idTipoPersona`, `nombre`, `apellido`, `fechaNac`, `documento`, `legajo`, `domicilio`, `telefono`, `email`, `genero`, `nacionalidad`, `alta`) VALUES (NULL, '0', 'Juan Marcelo', 'Oberauer', '1980-04-26', '28541788', '54128', 'Calle Paraguay 940', '(3751)42-4004', 'yomauser@gmail.com', 'Masculino', 'Paraguayo', '1');
        */
        String _err = "";
        Iterator it = _map.entrySet().iterator();
        String _nombreCampos = "";
        String _valorCampos = "";
        Boolean _primero = true;
        while (it.hasNext()) {
            HashMap.Entry pairs = (HashMap.Entry)it.next();
            _nombreCampos += (!_primero ? ",":"") + "`" + pairs.getKey() + "`";
            _valorCampos += (!_primero ? ",":"") + "'" + pairs.getValue() + "'";
            it.remove(); // avoids a ConcurrentModificationException
            _primero = false;
        }
        
        try
        {
            _ExecuteUpdate( "INSERT INTO " + this._dataBase + "." + _tabla + " (" + _nombreCampos + ") VALUES (" + _valorCampos + ")" );
        } catch (SQLException ex) {
            _err += "Error: No se pudo realizar insert. " + ex.getMessage();
        }
        return _err;
    }
    
    public String Update( String _tabla, HashMap<String, String> _map, String _where )
    {
         /*
        UPDATE `gestioncesmi`.`persona` SET `nombre` = 'elsa', `apellido` = 'Porrico', `domicilio` = 'La rioja 14', `telefono` = '4564521', `email` = 'tu@mail.com', `nacionalidad` = 'Peru' WHERE `persona`.`idPersona` = 4;
        */
        String _err = "";
        Iterator it = _map.entrySet().iterator();
        String _Campos = "";
        Boolean _primero = true;
        while (it.hasNext()) {
            HashMap.Entry pairs = (HashMap.Entry)it.next();
            _Campos += (!_primero ? ",":"") + "`" + pairs.getKey() + "` = '" + pairs.getValue() + "'";
            it.remove(); // avoids a ConcurrentModificationException
            _primero = false;
        }
        
        try
        {
            _ExecuteUpdate( "UPDATE " + this._dataBase + "." + _tabla + " SET " + _Campos + " WHERE " + _tabla + "." + _where);
        } catch (SQLException ex) {
            _err += "Error: No se pudo realizar Actualizacion. " + ex.getMessage();
        }
        return _err;
    }
}
